/***

select  rownumber() over (ORDER BY YASAL/100 DESC ) as Row, a.YAAN8, DATE(CHAR(YADOB+1900000)), DATE(CHAR(YADSI+1900000)) as Org_Strt_Dte, YADOB, YAALPH, b.YAJBCX, YASAL/100, YASAL/833552522
from C0053prdd.F060116 as a, C0053PRDD.F0101, C0053prdd.F060120 as b
WHERE 
	a.YAAN8=ABAN8
AND	a.YAAN8=b.YAAN8

AND	ABAT1='E' 
Order By YASAL DESC


go

select top 100 b.AlphaName, a.RemarkExplaination, a.DatePayCheck, a.AmountGrossPay , a.* 
from 
	rd4_dwcr.dbo.JDEPYEmployeeTransactionHist a,
	rd4_dwcr.dbo.JDEABAddressBookMaster b
where 
b.AddressNumber = a.AddressNumber 

and		a.RemarkExplaination not in ('Regular                       ', 'Holiday                       ','PDO                           ','OT 1.5                        ','OT 2.0                        ')

and RemarkExplaination = 'Emp Inc Plan                  '
and Year(a.DatePaycheck) >= 2013
and a.BusinessUnitCode ='         782'
--and a.AddressNumber=103558
Order By a.AmountGrossPay DESC
***/
USE RD_DWCR
GO

SELECT ROW_NUMBER() OVER (
		ORDER BY [RateSalaryAnnual] DESC
		) AS [Row],
	NTILE(100) OVER (
		ORDER BY [RateSalaryAnnual] DESC
		) AS [ Rank],
	b.AlphaName,
	a.BUSINESSUNITCODE,
	a.JobType,
	RateSalaryAnnual,
	a.category01payroll,
	CONVERT(DATE, DateBirth) AS [DOB],
	CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
	CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
	RatePreviousSalary
FROM JDEPYEmployeeMasterInfo a,
	JDEABAddressBookMaster b
WHERE b.[AddressNumber] = a.AddressNumber
	AND b.SearchTypeCode = 'E'
	AND a.BUSINESSUNITCODE = '         782'
ORDER BY RateSalaryAnnual DESC

--FOR XML AUTO, ROOT('Salary');
USE RD4_DWCR
GO

SELECT ROW_NUMBER() OVER (
		ORDER BY [RateSalaryAnnual] DESC
		) AS [Row],
	NTILE(100) OVER (
		ORDER BY [RateSalaryAnnual] DESC
		) AS [ Rank],
	b.AlphaName,
	a.BUSINESSUNITCODE,
	a.JobType,
	RateSalaryAnnual,
	a.category01payroll,
	CONVERT(DATE, DateBirth) AS [DOB],
	CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
	CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
	RatePreviousSalary
FROM JDEPYEmployeeMasterInfo a,
	JDEABAddressBookMaster b
WHERE b.[AddressNumber] = a.AddressNumber
	AND b.SearchTypeCode = 'E'
	AND a.BusinessUnitCode = '         782'
ORDER BY RateSalaryAnnual DESC

--Compute SUM(RateSalaryAnnual) By RateSalaryAnnual
--FOR XML AUTO, ROOT('Salary')
USE RD4_DWCR

SELECT *
FROM JDEPYEmployeeTransactionDetl
WHERE AddressNumber = 103558

SELECT AmountGrossPay,
	*
FROM JDEPYEmployeeTransactionHist
WHERE RemarkExplaination = 'Emp Inc Plan                  '
	AND BusinessUnitCode = '         782'

SELECT *
FROM JDEPYEmployeeTransactionDetl
WHERE AddressNumber = 103558

SELECT AmountGrossPay, b.AlphaName, a.RemarkExplaination,
	a.*
FROM JDEPYEmployeeTransactionHist a, JDEABAddressBookMaster b

WHERE 
FiscalYear=13 and b.[AddressNumber] = a.AddressNumber and
a.BusinessUnitCode = '         782' 
order by a.RemarkExplaination